﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text; 
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web;
using System.Web.UI.WebControls;

namespace LongNet.Util
{
    public static class SqlClass
    { 
              

        public static SqlTransaction BeginSqlTransaction()
        {
            SqlConnection sqlCon = new SqlConnection(Util.SqlHelper.ConnectionString);
            if (sqlCon.State == ConnectionState.Closed)
                sqlCon.Open();
            return sqlCon.BeginTransaction();
        }
        /// <summary>
        /// 获取Excel档案数据到DataSet中
        /// </summary>
        /// <param name="ExcelPath">Excel文件所在路径</param>
        /// <param name="SheetName">工作薄名称</param>
        /// <returns></returns>
        public static Result GetExcelData(string ExcelPath,string SheetName,string strOrderBy)
        {
            string strSql = "";

            if (string.IsNullOrEmpty(strOrderBy))
            {
                strOrderBy = "";
            }

            strSql = "select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=" + ExcelPath + "',' select * from [" + SheetName + "$] " + strOrderBy + "') ";

            

            return GetDataSet(strSql);
        }
        /// <summary>
        /// 执行SQL语句返回DataSet
        /// </summary>
        /// <param name="strSql">SQL语句,一定要防SQL注入攻击</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static Result GetDataSet(string strSql)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            if (sqlCon.State == System.Data.ConnectionState.Closed)
            {
				sqlCon.Open();
            }
            Result rs = new Result();

            try
            {
                DataSet ds = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);

                rs.IsSuccess = true;

                rs.DataResult = ds;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }

				sqlCon.Dispose();
                
            }
            return rs;

        }
        /// <summary>
        /// 执行Sql以填充Result中的DataSet
        /// </summary>
        /// <param name="strSql">数据库Sql</param>
        /// <param name="rs">需要填充的结果集</param>
        /// <returns></returns>
        public static bool GetDataSet(string strSql,Result rs)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
            try
            {
                if (sqlCon.State == System.Data.ConnectionState.Closed)
                {
                    sqlCon.Open();
                }
                rs.DataResult = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);

                rs.IsSuccess = true;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;

                rs.IsSuccess = false;

                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
            return rs.IsSuccess;

        }
        /// <summary>
        /// 执行SQL语句返回DataSet
        /// </summary>
        /// <param name="strSql">SQL语句,一定要防SQL注入攻击</param>
        /// <param name="m_SqlParameters">Sql参数数组</param>
        /// <returns></returns>
        public static Result GetDataSet(string strSql,SqlParameter[] m_SqlParameters)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            if (sqlCon.State == System.Data.ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            Result rs = new Result();
            try
            {
                DataSet ds = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql, m_SqlParameters);

                rs.IsSuccess = true;

                rs.DataResult = ds;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
            return rs;

        }
        /// <summary>
        /// 根据查询的关键字获取到数据列表
        /// </summary>
        /// <param name="strTableName">查询的表名</param>
        /// <param name="strKey">要查询的关键字</param>
        /// <param name="m_ParList">要查询的列名和值（值如果为空串则使用strKey作为查询条件）</param>
        /// <param name="m_SqlDbTypes">要作为查询字段的Sql类型数组</param>
        /// <param name="strOpreate">查询的操作符，如=，like,>= 等比较符号</param>
        /// <returns></returns>
        public static Result GetDataListBySearch(string strSelectSql,string strKey,Dictionary<string, object> m_ParList, System.Data.SqlDbType[] m_SqlDbTypes,string[] strOpreate)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            Result rs = new Result();
            
            string strSql = strSelectSql;
            try
            {
                if (sqlCon.State == System.Data.ConnectionState.Closed)
                {
                    sqlCon.Open();
                }
                if (m_ParList != null)
                {
                    SqlParameter[] m_SqlParameters = new SqlParameter[m_ParList.Count];

                    int i = 0;

                    foreach (KeyValuePair<string, object> m_Entry in m_ParList)
                    {
                        m_SqlParameters[i] = new SqlParameter("@" + m_Entry.Key, m_SqlDbTypes[i]);

                        if (m_Entry.Value.ToString() == "")
                        {
                            m_SqlParameters[i].Value = strKey;
                        }
                        else
                        {
                            m_SqlParameters[i].Value = m_Entry.Value;
                        }
                        if (strOpreate[i].Trim().ToLower() == "like")
                        {
                            if (i == 0)
                            {
                                strSql += " Where " + m_Entry.Key + " " + strOpreate[i] + " '%'+" + m_SqlParameters[i].ParameterName + "+'%'";
                            }
                            else
                            {
                                strSql += " or " + m_Entry.Key + " " + strOpreate[i] + " '%'+" + m_SqlParameters[i].ParameterName + "+'%'";
                            }
                        }
                        else if (m_SqlDbTypes[i] == SqlDbType.DateTime)
                        {
                            if (i == 0)
                            {
                                strSql += " Where " + m_Entry.Key + strOpreate[i] + m_SqlParameters[i].ParameterName;
                            }
                            else
                            {
                                strSql += " or " + m_Entry.Key + strOpreate[i] + m_SqlParameters[i].ParameterName;
                            }
                        }
                        i++;
                    }
                    DataSet ds = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql, m_SqlParameters);

                    rs.IsSuccess = true;

                    rs.DataResult = ds;
                }
                else
                {
                    DataSet ds = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);

                    rs.IsSuccess = true;

                    rs.DataResult = ds;
                }
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
            return rs;
        }
        public static Result GetDataSet(SqlTransaction tran,string strSql)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
            if (sqlCon.State == System.Data.ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            Result rs = new Result();
            try
            {                
                 DataSet ds = SqlHelper.ExecuteDataset(tran, CommandType.Text, strSql);
                rs.IsSuccess = true;
                rs.DataResult = ds;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
            return rs;

        }

         
        /// <summary>
        /// 执行SQL语句返回影响行数,如添加删除等
        /// </summary>
        /// <param name="strSql">sql语句,一定要防SQL注入攻击</param>
        /// <param name="effect">输出行数</param>
        /// <returns></returns>
        public static  Result  GetEffects(string strSql)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            SqlTransaction trans = sqlCon.BeginTransaction();

            Result rs = new Result();
            try
            {
                int effect = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql);

                if (effect > 0)
                {
                    rs.IsSuccess = true;
                    rs.Msg = "影响行数:"+effect .ToString ();
                    trans.Commit();
                }
                else
                {
                    rs.IsSuccess = false;
                    rs.Msg = "影响0行";
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
            }
            finally
            {
                if (sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();

				trans.Dispose();
            }
            return rs;
        }
        /// <summary>
        /// 执行SQL语句返回SqlDataReader
        /// </summary>
        /// <param name="strsql">sql语句,一定要防SQL注入攻击</param>
        /// <returns></returns>
        public static  SqlDataReader GetDataReader(string strsql)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }
            try
            {
                return SqlHelper.ExecuteReader(sqlCon, CommandType.Text, strsql);
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
        }

        /// <summary>
        /// 执行SQL语句返回SqlDataReader
        /// </summary>
        /// <param name="strsql">sql语句,一定要防SQL注入攻击</param>
        /// <returns></returns>
        public static  SqlDataReader GetDataReader(string strsql,bool close)
        {
           SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            }          
            try
            {
                SqlCommand cmd = new SqlCommand(strsql, sqlCon);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                sqlCon.Close();
                return null;
            }
        }
        /// <summary>
        /// 执行SQL语句返回object 
        /// </summary>
        /// <param name="strsql">sql语句,一定要防SQL注入攻击</param>
        /// <returns></returns>
        public static  object GetObject(string strsql)
        {
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            } 
            try
            {
                return SqlHelper.ExecuteScalar(sqlCon, CommandType.Text, strsql);
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
        }
       public static Result GetModuleByUserID(int UserId)
        {
            Result rs = new Result();
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
           string strSql = "";
           if (UserId != -1)
           {
               strSql = "select distinct ModuleCategory.* from dbo.ModuleCategory inner join dbo.RoleModule on ModuleCategory.id = dbo.RoleModule.ModuleId "
                       + "inner join dbo.RoleUser on dbo.RoleUser.RoleId = dbo.RoleModule.RoleId where   RoleUser.UserId = " + UserId.ToString() + " order by ModuleCategory.parentid,OrderNum asc";
              
           }
           else
           {
               strSql = "select ModuleCategory.* from dbo.ModuleCategory order by OrderNum asc";
           }
            if (sqlCon.State == ConnectionState.Closed)
            {
                sqlCon.Open();
            } 
           try
            {
                rs.DataResult = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                rs.IsSuccess = false;
                rs.Msg = ex.Message;
            }
           finally
           {
               if (sqlCon.State == ConnectionState.Open)
               {
                   sqlCon.Close();
               }
               sqlCon.Dispose();
           }
            return rs;
        }
        
        /// <summary>
        /// 通过用户ID及模块ID返回菜单列表
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="modelID"></param>
        /// <returns></returns>
       public static string GetMenuList1(int userid, int modelID)
       {
           string menulist = "";
           int subcount = 0;           
           Result rs = new Result();
           rs = SqlClass.GetModuleByUserID(userid);

           DataSet ds = new DataSet();
           ds = rs.DataResult;
           DataView dv = ds.Tables[0].DefaultView;
           for (int i = 0; i < dv.Count; i++)
           {
               int pid = Convert.ToInt32(dv[i]["ParentId"]);
               if (pid == modelID)
               {
                   subcount++;
                   int id = Convert.ToInt32(dv[i]["id"]);
                   //一级目录
                   menulist += "<tr>" +
                               "  <td id=\"td" + subcount.ToString() + "\" class=\"menuClose\"   onclick=\"HideMenu('td" + subcount.ToString() + "','tr" + subcount.ToString() + "')\">" +
                               "      <div class=\"menuTitleImg\"><img src=\"" + dv[i]["ImageUrl"].ToString() + "\" /></div> " +
                               "      <div class=\"menuTitle\">" + dv[i]["Name"].ToString() + " </div> " +
                               "   </td>    " +
                               "</tr>\r\n";

                   menulist += "<tr id=\"tr" + subcount.ToString() + "\" style=\"display:none\"> <td> ";
                   //下面找ParentId为上面ID的结点
                   for (int j = 0; j < dv.Count; j++)
                   {
                       int subPid = Convert.ToInt32(dv[j]["ParentId"]);
                       if (subPid == id)
                       { 
                           menulist += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
                       }
                   }
                   menulist += "</td></tr>	\r\n";
               }

           }
           return menulist;
       }
       /// <summary>
       /// 通过用户ID及模块ID返回菜单列表
       /// </summary>
       /// <param name="userid">用户ID</param>
       /// <param name="modelID">模块ID</param>
       /// <returns></returns>
       public static string GetMenuList(int userid, int modelID)
       {
           DataSet dsall = GetList(modelID).DataResult;
           DataSet dsModel = GetModuleIDs(userid).DataResult;
           DataView dv = dsall.Tables[0].DefaultView;
           DataView dvIds = dsModel.Tables[0].DefaultView;
           string menulist = "";
           int subcount = 0;
         
           int parentid = -1;
           Hashtable ht = new Hashtable();//是否已存放了一级
           for (int i = 0; i < dv.Count; i++)
           {  
               parentid = Convert.ToInt16(dv[i]["parentID"]);
               if (!ht.Contains(parentid.ToString()))
               {
                   string imageurl = dv[i]["ImageUrl"].ToString();
                   if (imageurl == "")
                   {
                       imageurl = "00";
                   }
                   ht.Add(parentid.ToString(), parentid.ToString());
                   subcount++;                
                   //一级目录
                   menulist += "<tr>" +
                               "  <td id=\"td" + subcount.ToString() + "\" class=\"menuClose\"   onclick=\"HideMenu('td" + subcount.ToString() + "','tr" + subcount.ToString() + "')\">" +
                               "      <div class=\"menuTitleImg\"><img src=\"" + dv[i]["modelImage"].ToString() + "\" /></div> " +
                               "      <div class=\"menuTitle\">" + dv[i]["subModelName"].ToString() + " </div> " +
                               "   </td>    " +
                               "</tr>\r\n";
                   menulist += "<tr id=\"tr" + subcount.ToString() + "\" style=\"display:none\"> <td> ";
                   //下面找ParentId为上面ID的结点
                   if (userid == 1)
                   {
                       menulist += GetMenu(dv, parentid, dvIds,1);
                   }
                   else
                   {
                       menulist += GetMenu(dv, parentid, dvIds);
                   }
                   menulist += "</td></tr>	\r\n";               
               }   
           }
           //系统菜单
           string systemMenu = "<tr>  <td id=\"tdSystemMenu\" class=\"menuClose\"   onclick=\"HideMenu('tdSystemMenu','trSystemMenu')\">      <div class=\"menuTitleImg\"><img src=\"../images/menuLogo/18.gif\" /></div>       <div class=\"menuTitle\">系统设置 </div></td></tr><tr id=\"trSystemMenu\" style=\"display:none\"> <td> <div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"../usermanage/userpassword.aspx\" > 修改密码</a></div><div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"../usermanage/userMyinfo.aspx\" > 个人信息管理</a></div>	</td></tr>";
           menulist += systemMenu;
           return menulist;
       } 
       public static string GetMenu(DataView dv, int parentid, DataView dvids)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               {
                   bool b = false;
                   for (int i = 0; i < dvids.Count; i++)
                   {
                       if (dvids[i]["ModuleId"].ToString() == dv[j]["id"].ToString())
                       {
                           b = true;
                           break;
                       }
                   }
                   if (b)
                   {
                       list += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
                   }
                   else
                   {
                       string IsShowControl = "";

                       if (Util.DataConvert.GetInt32Value(System.Configuration.ConfigurationManager.AppSettings["IsMenuItemShow"]) == -1)
                       {
                           IsShowControl = " style='display:none'";
                       }
                       list += "<div class=\"menuItemFalse\" " + IsShowControl + ">·  " + dv[j]["Name"].ToString() + " </div>	\r\n";
                   }
               }
           }
           return list;
       }


       /// <summary>
       /// 通过用户ID及模块ID返回菜单列表
       /// </summary>
       /// <param name="userid">用户ID</param>
       /// <param name="modelID">模块ID</param>
       /// <returns></returns>
       public static DataTable GetMenuList(int userid, int modelID, bool IsNew)
       {
           DataSet dsall = GetList(modelID).DataResult;
           DataSet dsModel = GetModuleIDs(userid).DataResult;
           DataView dv = dsall.Tables[0].DefaultView;
           DataView dvIds = dsModel.Tables[0].DefaultView;
           string menulist = "";
           int subcount = 0;

           DataTable MenuTable = new DataTable("Menu");
           DataColumn menuCol = MenuTable.Columns.Add("id", typeof(Int32));
           //menuCol.AllowDBNull = false;
           //menuCol.Unique = true;
           MenuTable.Columns.Add("ParentID", typeof(Int32));
           MenuTable.Columns.Add("ModelName", typeof(String));
           MenuTable.Columns.Add("modelImage", typeof(String));
           MenuTable.Columns.Add("URL", typeof(String));
           DataRow CurrentRow;

           int parentid = -1;
           Hashtable ht = new Hashtable();//是否已存放了一级
           for (int i = 0; i < dv.Count; i++)
           {
               parentid = Convert.ToInt16(dv[i]["parentID"]);
               if (!ht.Contains(parentid.ToString()))
               {
                   string imageurl = dv[i]["ImageUrl"].ToString();
                   if (imageurl == "")
                   {
                       imageurl = "00";
                   }
                   ht.Add(parentid.ToString(), parentid.ToString());
                   subcount++;

                   //
                   CurrentRow = MenuTable.NewRow();
                   CurrentRow["id"] = dv[i]["id"];
                   CurrentRow["ParentID"] = 0;
                   CurrentRow["ModelName"] = dv[i]["subModelName"];
                   CurrentRow["modelImage"] = dv[i]["modelImage"];
                   CurrentRow["URL"] = "";
                   MenuTable.Rows.Add(CurrentRow);

                   menulist = string.Empty;
                   //下面找ParentId为上面ID的结点
                   if (userid == 1)
                   {
                       menulist += GetMenu(dv, parentid, PublicClass.GetInt32Value(dv[i]["id"]), subcount, dvIds, 1);
                   }
                   else
                   {
                       menulist += GetMenu(dv, parentid, PublicClass.GetInt32Value(dv[i]["id"]), subcount, dvIds);
                   }
                   if (!string.IsNullOrEmpty(menulist)) menulist = menulist.Remove(0, 1);
                   String[] subMenuArr = menulist.Split(';');
                   for (int k = 0; k < subMenuArr.Length; k++)
                   {
                       String[] subRowArr = subMenuArr[k].Split(',');
                       if (subRowArr.Length == 5)
                       {
                           CurrentRow = MenuTable.NewRow();
                           subcount++;
                           CurrentRow["id"] = PublicClass.GetInt32Value(subRowArr[0]);
                           CurrentRow["ParentID"] = PublicClass.GetInt32Value(subRowArr[1]);
                           CurrentRow["ModelName"] = subRowArr[2];
                           CurrentRow["modelImage"] = subRowArr[3];
                           CurrentRow["URL"] = subRowArr[4];
                           MenuTable.Rows.Add(CurrentRow);
                       }
                   }
               }
           }
           return MenuTable;
       }

       public static string GetMenu(DataView dv, int parentid, int CurrentParentId, int OriginalNubmer, DataView dvids)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               {
                   bool b = false;
                   for (int i = 0; i < dvids.Count; i++)
                   {
                       if (dvids[i]["ModuleId"].ToString() == dv[j]["id"].ToString())
                       {
                           b = true;
                           break;
                       }
                   }
                   if (b)
                   {
                       list += string.Format(";{0},{1},{2},{3},{4}", dv[j]["id"].ToString(), CurrentParentId, dv[j]["Name"].ToString(), "", dv[j]["url"].ToString());
                   }
                   else
                   {
                       string IsShowControl = "";

                       if (Util.DataConvert.GetInt32Value(System.Configuration.ConfigurationManager.AppSettings

   ["IsMenuItemShow"]) == -1)
                       {
                           IsShowControl = " style='display:none'";
                       }
                   }
               }
           }
           return list;
       }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="dv"></param>
       /// <param name="parentid"></param>
       /// <param name="dvids"></param>
       /// <param name="power">是否所有权限</param>
       /// <returns></returns>
       public static string GetMenu(DataView dv, int parentid, int CurrentParentId, int OriginalNubmer, DataView dvids, int power)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               {
                   list += string.Format(";{0},{1},{2},{3},{4}", dv[j]["id"].ToString(), CurrentParentId, dv[j]["Name"].ToString(), "", dv[j]["url"].ToString());
               }
           }
           return list;
       }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dv"></param>
        /// <param name="parentid"></param>
        /// <param name="dvids"></param>
        /// <param name="power">是否所有权限</param>
        /// <returns></returns>
        public static string GetMenu(DataView dv, int parentid, DataView dvids,int power)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               { 
                   list += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
                }
           }
           return list;
       }
       /// <summary>
       /// 开发商菜单
       /// </summary> 
       /// <param name="modelID">模块ID</param>
       /// <returns></returns>
       public static string GetCompanyMenuList(int modelID)
       {
           DataSet dsall = GetList(modelID).DataResult; 
           DataView dv = dsall.Tables[0].DefaultView; 
           string menulist = "";
           int subcount = 0; 
           int parentid = -1;
           Hashtable ht = new Hashtable();//是否已存放了一级
           for (int i = 0; i < dv.Count; i++)
           {
               parentid = Convert.ToInt16(dv[i]["parentID"]);
               if (!ht.Contains(parentid.ToString()))
               {
                   string imageurl = dv[i]["ImageUrl"].ToString();
                   if (imageurl == "")
                   {
                       imageurl = "00";
                   }
                   ht.Add(parentid.ToString(), parentid.ToString());
                   subcount++;
                   //一级目录
                   menulist += "<tr>" +
                               "  <td id=\"td" + subcount.ToString() + "\" class=\"menuClose\"   onclick=\"HideMenu('td" + subcount.ToString() + "','tr" + subcount.ToString() + "')\">" +
                               "      <div class=\"menuTitleImg\"><img src=\"" + dv[i]["modelImage"].ToString() + "\" /></div> " +
                               "      <div class=\"menuTitle\">" + dv[i]["subModelName"].ToString() + " </div> " +
                               "   </td>    " +
                               "</tr>\r\n";
                   menulist += "<tr id=\"tr" + subcount.ToString() + "\" style=\"display:none\"> <td> ";
                   //下面找ParentId为上面ID的结点
                   menulist += GetCompanyMenu(dv, parentid);
                   menulist += "</td></tr>	\r\n";
               }

           }
           return menulist;
       }

        //
        /// <summary>
       /// 开发商菜单列表
        /// </summary>
        /// <param name="dv"></param>
        /// <param name="parentid"></param>
        /// <returns></returns>
       public static string GetCompanyMenu(DataView dv, int parentid)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               {
                   list += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
               }
           }
           return list;
       }

        /// <summary>
        /// 根据大模块ID返回所有功能列表
        /// </summary>
        /// <param name="modelID"></param>
        /// <returns></returns>
       public static Result GetList(int modelID)
       {
           string sqlstr = " SELECT ModuleCategory.* , SubModel.imageurl as modelImage, SubModel.Name AS subModelName,SubModel.id as submodelID,submodel.ordernum as subOrderNum "
                            + " FROM ModuleCategory INNER JOIN "
                             + " ModuleCategory AS SubModel ON ModuleCategory.ParentId = SubModel.Id INNER JOIN "
                             + " ModuleCategory AS Model ON SubModel.ParentId = Model.Id where model.id=" + modelID.ToString() + "  and ModuleCategory.state>-1 order by SubModel.ordernum,ModuleCategory.ordernum";
           return SqlClass.GetDataSet(sqlstr);       
       }

        /// <summary>
        /// 根据用户ID返回功能的ID列表
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
       public static Result GetModuleIDs(int userID)
       {
           string sqlstr = "  SELECT distinct RoleModule.ModuleId  " +
                            "    FROM RoleUser INNER JOIN " +
                            "   RoleModule ON RoleUser.RoleId = RoleModule.RoleId INNER JOIN " +
                            "  ModuleCategory ON RoleModule.ModuleId = ModuleCategory.Id  " +
                            "  where userid=" + userID.ToString() +
                       " union select ModuleCategory.Id  from UserPower INNER JOIN   ModuleCategory ON UserPower.PowerId = ModuleCategory.Id " +
                                " where userId=" + userID.ToString();
           return SqlClass.GetDataSet(sqlstr);
       }

 


      
        /// <summary>
        /// 返回在线用户数
        /// </summary>
        /// <returns></returns>
       public static int OnlineUsers()
       {
           return 0;
       }

        public static void AddTree(int ParentID, TreeNode pNode,DataSet ds,TreeView treeView)
        {
            DataRow[] drs = ds.Tables[0].Select("ParentID=" + ParentID);
            foreach (DataRow dr in drs)
            {
                TreeNode Node = new TreeNode();
                if (pNode == null)                  
                {
                    Node.Value = dr["Id"].ToString(); 
                    Node.Text = dr["Name"].ToString();
                    treeView.Nodes.Add(Node);
                    AddTree(Convert.ToInt32(dr["Id"]), Node,ds,treeView); //递归
                }
                else //处理子节点
                {
                    Node.Value = dr["Id"].ToString();
                    Node.Text = dr["Name"].ToString();
                    pNode.ChildNodes.Add(Node);
                    AddTree(Convert.ToInt32(dr["Id"]), Node, ds, treeView); //递归
                }
            }
        }
        /// <summary>
        /// 返回公告信息
        /// </summary>
        /// <returns></returns>
        public static string GetAnnounce()
        {
            return "公告信息：欢迎使用房产信息管理系统";
        }
        /// <summary>
        /// 返回待办事项数
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <returns></returns>
        public static string GetWorks(int uid)
        {
            return "0";
        }
        public static string GetMessages(int uid)
        {
            return "<items>" +
                        "<item><name>新消息1</name></item>" +
                        "<item><name>新消息2</name></item>" +
                        "<item><name>新消息3</name></item>" +
                        "<item><name>新消息4</name></item>" +
                    "</items>";
        }
        /// <summary>
        /// 返回基础数据分类
        /// </summary>
        /// <param name="categoryName"></param>
        /// <returns></returns>
        public static Result GetBasicCategory(string categoryName)
        {
            string sqlstr = "select * from BasicCategory where  categorytype='" + categoryName.Replace("'", "''").Trim() + "'";
            return SqlClass.GetDataSet(sqlstr);

        }
        public static string GetBasicCategory(int id)
        {
            string sqlstr = "select top 1 CategoryName  from BasicCategory where  id=" + id.ToString();
            return SqlClass.GetObject(sqlstr).ToString();

        }

        //public static bool BindListFromCategory(string CategoryTypeCode, string Filter,Coolite.Ext.Web.ComboBox cmb)
        //{
        //    if (cmb == null)
        //        return true;
        //    if (HttpContext.Current.Session["UserEntityInfo"] == null)
        //        return false;
        //    UserEntityInfo  userEntityInfo= HttpContext.Current.Session["UserEntityInfo"] as UserEntityInfo;
        //    try
        //    {               
        //        string strWhere = "";
        //        if (userEntityInfo.CityId>0)
        //        {
        //            strWhere = " ( CityId=0 or CityId=" + userEntityInfo.CityId + " ) ";
        //        }

        //        Dictionary<string, string> dic = GetCategorySettings(CategoryTypeCode, strWhere + Filter);
        //        foreach (var item in dic)
        //        {
        //            cmb.Items.Add(new Coolite.Ext.Web.ListItem(item.Value, item.Key));
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return true;

        //}

        //public static bool BindListFromCategory(string CategoryTypeCode, string Filter, Coolite.Ext.Web.ComboBox cmb, string selectItemValue)
        //{
        //    if (cmb == null)
        //        return true;
        //    if (HttpContext.Current.Session["UserEntityInfo"] == null)
        //        return false;
        //    UserEntityInfo userEntityInfo = HttpContext.Current.Session["UserEntityInfo"] as UserEntityInfo;
        //    try
        //    {
        //        string strWhere = "";
        //        if (userEntityInfo.CityId > 0)
        //        {
        //            strWhere = " ( CityId=0 or CityId=" + userEntityInfo.CityId + " ) ";
        //        }

        //        Dictionary<string, string> dic = GetCategorySettings(CategoryTypeCode, strWhere + Filter);
        //        foreach (var item in dic)
        //        {

        //            cmb.Items.Add(new Coolite.Ext.Web.ListItem(item.Value, item.Key));
        //            if (!string.IsNullOrEmpty(selectItemValue)&&item.Key==selectItemValue)
        //            {
        //                cmb.SelectedIndex = cmb.Items.Count - 1;
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return true;

        //}

        //绑定Web 下拉框
        public static bool BindListFromCategory(string CategoryTypeCode, string Filter,System.Web.UI.WebControls.ListControl m_ListControl, string selectItemValue)
        {
            if (m_ListControl == null)
                return true;
            if (HttpContext.Current.Session["UserEntityInfo"] == null)
                return false;
            UserEntityInfo userEntityInfo = HttpContext.Current.Session["UserEntityInfo"] as UserEntityInfo;
            try
            {
                string strWhere = "";
                if (userEntityInfo.CityId > 0)
                {
                    strWhere = " ( CityId=0 or CityId=" + userEntityInfo.CityId + " ) ";
                }

                Dictionary<string, string> dic = GetCategorySettings(CategoryTypeCode, strWhere + Filter);
                foreach (var item in dic)
                {
                    m_ListControl.Items.Add(new System.Web.UI.WebControls.ListItem(item.Value, item.Key));
                }
                if (!string.IsNullOrEmpty(selectItemValue))
                {
                    m_ListControl.SelectedValue = selectItemValue;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;

        }


        /// <summary>
        /// 根据表里的ID查找表中的字段
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="TextField">字段信息</param>
        /// <param name="filter">过滤的信息</param>
        /// <returns>取得的字段信息</returns>
        public static string GetTableFieldById(string TableName, string TextField, string Filter)
        {
            if (string.IsNullOrEmpty(TableName.Trim()) || string.IsNullOrEmpty(TextField.Trim()))
            {
                return "";
            }
            try
            {

                string strSql = "Select Id," + TextField.Replace("'", "''").Trim() + " From  " + TableName.Replace("'", "''").Trim();
                if (!string.IsNullOrEmpty(Filter.Trim()))
                {
                    strSql += " Where  " + Filter;
                }
                Result rs = SqlClass.GetDataSet(strSql);
                if (!rs.IsSuccess)
                {
                    return "";
                }
                if (rs.DataResult != null && rs.DataResult.Tables[0].Rows.Count > 0)
                {
                    string value = "";
                    for (int i = 0; i < rs.DataResult.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = rs.DataResult.Tables[0].Rows[i];
                        if(i == 0)
                            value = dr[TextField].ToString();
                        else
                            value += "," + dr[TextField].ToString();
                    }
                    return value;
                }
                return "";
            }
            catch (Exception ex)
            {
                return "";
            }
            

        }

        //public static bool BindListFromTable(string TableName,string TextField, string Filter, Coolite.Ext.Web.ComboBox cmb)
        //{
        //    if (cmb == null)
        //        return true;
        //    if (string.IsNullOrEmpty(TableName.Trim()) || string.IsNullOrEmpty(TextField.Trim()))
        //    {
        //        throw new Exception("参数错误！请检查表名和显示字段！");
        //    }
        //    try
        //    {

        //        string strSql = "select Id," + TextField.Replace("'", "''").Trim() + " from  " + TableName.Replace("'", "''").Trim();
        //        if (!string.IsNullOrEmpty(Filter.Trim()))
        //        {
        //            strSql += " where  " + Filter.Replace("'", "''").Trim();
        //        }
        //        Result rs = SqlClass.GetDataSet(strSql);
        //        if (!rs.IsSuccess)
        //        {
        //            throw new Exception("查询数据错误！");
        //        }
        //        if (rs.DataResult != null && rs.DataResult.Tables[0].Rows.Count > 0)
        //        {
        //            for (int i = 0; i < rs.DataResult.Tables[0].Rows.Count; i++)
        //            {
        //                DataRow dr = rs.DataResult.Tables[0].Rows[i];
        //                if (dr[TextField] != DBNull.Value)
        //                {
        //                    cmb.Items.Add(new Coolite.Ext.Web.ListItem(dr[TextField].ToString(),dr["Id"].ToString()));                            
        //                }
        //            }
        //        }              
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return true;

        //}

        private  static Dictionary<string, string> GetCategorySettings(string CategoryTypeCode, string Filter)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            try
            {
                string strSql = "select * from CategorySettings where  CategoryTypeCode='" + CategoryTypeCode.Replace("'", "''").Trim() + "'";
                if (!string.IsNullOrEmpty(Filter.Trim()))
                {
                    strSql += " and " + Filter.Replace("'", "''").Trim();
                }
                Result rs = SqlClass.GetDataSet(strSql);
                if (!rs.IsSuccess)
                {
                    throw new Exception("查询数据错误！");
                }
                if (rs.DataResult != null && rs.DataResult.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i <  rs.DataResult.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = rs.DataResult.Tables[0].Rows[i];
                        if(dr["CategoryValue"]!=DBNull.Value)
                        {
                            dic.Add(dr["CategoryValue"].ToString(), dr["CategoryText"].ToString());
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            return dic;
        }

        /// <summary>
        /// 根据分类代码以及类型Value获取显示的文本
        /// </summary>
        /// <Memo>彭承龙 2010-09-15</Memo>
        /// <param name="CategoryTypeCode">分类代码</param>
        /// <param name="CategoryValue">分类的值</param>
        /// <returns></returns>
        public static string GetCategoryTextByValue(string CategoryTypeCode, string CategoryValue)
        {
            string CategoryText = "";
            try
            {

                GetCategorySettings(CategoryTypeCode, "").TryGetValue(CategoryValue, out CategoryText);
            }
            catch (Exception ex)
            {               
               
            }
            return CategoryText;
        }
        
        public static Dictionary<string, string> GetCategoryFromXml(string Key,string Filter)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
            try
            {
                System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
                xmlDoc.Load(InitInfo.XmlPath);
                System.Xml.XmlNodeList xnodlist = xmlDoc.SelectNodes("combos");
                System.Xml.XmlNode xmlNode = xnodlist[0].SelectSingleNode("combo[@id=\"" + Key + "\"]");
                if (xmlNode.Attributes["type"].Value == "TABLE")
                {
                    sqlCon.Open();
                    string strSql = "select * from " + xmlNode.Attributes["table"].Value + " " + Filter;

                    DataSet ds = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        dic.Add(dr[xmlNode.Attributes["keyColumn"].Value].ToString(), dr[xmlNode.Attributes["column"].Value].ToString());
                    }
                }
                if (xmlNode.Attributes["type"].Value == "CODE")
                {
                    xnodlist = xmlNode.ChildNodes;
                    foreach (System.Xml.XmlNode childNode in xmlNode.ChildNodes)
                    {
                        dic.Add(childNode.Attributes["value"].Value, childNode.InnerText);
                    }
                }
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                throw ex;
            }
            finally
            {
                if (sqlCon.State == ConnectionState.Open) sqlCon.Close();
                sqlCon.Dispose();
            } 
            return dic;

        }

        /// <summary>
        /// 直接从xml文件绑定到list控件 
        /// </summary>
        /// <author>彭承龙，防止value重复dic错误</author>
        /// <param name="Key"></param>
        /// <param name="Filter"></param>
        /// <param name="m_ListControl"></param>
        public static void BingListFromXml(string Key, string Filter, System.Web.UI.WebControls.ListControl m_ListControl)
        {          
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
            try
            {
                System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
                xmlDoc.Load(InitInfo.XmlPath);
                System.Xml.XmlNodeList xnodlist = xmlDoc.SelectNodes("combos");
                System.Xml.XmlNode xmlNode = xnodlist[0].SelectSingleNode("combo[@id=\"" + Key + "\"]");
                if (xmlNode.Attributes["type"].Value == "TABLE")
                {
                    sqlCon.Open();
                    string strSql = "select * from " + xmlNode.Attributes["table"].Value + " " + Filter;

                    DataSet ds = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {                       
                        m_ListControl.Items.Add(new ListItem(dr[xmlNode.Attributes["keyColumn"].Value].ToString(), dr[xmlNode.Attributes["column"].Value].ToString()));
                    }
                }
                if (xmlNode.Attributes["type"].Value == "CODE")
                {
                    xnodlist = xmlNode.ChildNodes;
                    foreach (System.Xml.XmlNode childNode in xmlNode.ChildNodes)
                    {
                         m_ListControl.Items.Add(new ListItem(childNode.InnerText,childNode.Attributes["value"].Value));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

            }
        

        }
        public static string GetNameByID(int id, string typeId)
        {
            try
            {
                return GetCategoryFromXml(typeId, "")[id.ToString()];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

            }
        }


        public static void BindCategoryFromXml(string Key, string Filter, System.Web.UI.WebControls.ListControl m_ListControl)
        {
            BindCategoryFromXml(Key, Filter, m_ListControl, null); 
        }

        ///// <summary>
        ///// 加载列表，同时默认选择一个值
        ///// 修改人：喻园
        ///// 修改时间：2010-05-31 11:39
        ///// </summary>
        ///// <param name="Key"></param>
        ///// <param name="Filter"></param>
        ///// <param name="m_ListControl"></param>
        ///// <param name="selectItemValue"></param>
        //public static void BindCategoryFromXml(string Key, string Filter,Coolite.Ext.Web.ComboBox m_ListControl, string selectItemValue)
        //{
        //    Dictionary<string, string> m_Dic = GetCategoryFromXml(Key, Filter);

        //    int i = 0;
        //    foreach (string strKey in m_Dic.Keys)
        //    {
        //        m_ListControl.Items.Add(new Coolite.Ext.Web.ListItem(m_Dic[strKey], strKey));
        //        if (i++ == 0 && (selectItemValue == null || selectItemValue.Trim() == ""))
        //        {
        //            selectItemValue = strKey;
        //        }
        //    }
        //    m_ListControl.SelectedItem.Value = selectItemValue;             
        //}



        /// <summary>
        /// 加载列表，同时默认选择一个值
        /// 修改人：喻园
        /// 修改时间：2010-05-31 11:39
        /// </summary>
        /// <param name="Key"></param>
        /// <param name="Filter"></param>
        /// <param name="m_ListControl"></param>
        /// <param name="selectItemValue"></param>
        public static void BindCategoryFromXml(string Key,string Filter,System.Web.UI.WebControls.ListControl m_ListControl, string selectItemValue)
        {
            Dictionary<string, string> m_Dic = GetCategoryFromXml(Key, Filter);
            
            int i = 0;
            foreach (string strKey in m_Dic.Keys)
            {
                m_ListControl.Items.Add(new ListItem(m_Dic[strKey], strKey));
                if(i++ == 0 && (selectItemValue == null || selectItemValue.Trim() == ""))
                {
                    selectItemValue = strKey;
                }
            }
            m_ListControl.SelectedValue = selectItemValue;
        }
        /// <summary>
        /// 绑定XML中的数据到列表控件
        /// </summary>
        /// <param name="m_Dic">Dictionary键值对</param>
        /// <param name="m_ListControl">列表框控件</param>
        public static void BindDictionaryToListControl(Dictionary<string, string> m_Dic, System.Web.UI.WebControls.ListControl m_ListControl)
        {
            m_ListControl.Items.Clear();
            foreach (string strKey in m_Dic.Keys)
            {
                m_ListControl.Items.Add(new ListItem(m_Dic[strKey], strKey));
            }
        }
        /// <summary>
        /// 根据查询条件获取所有相关联业务信息
        /// </summary>
        /// <param name="strMappingNo">测绘编号</param>
        /// <param name="strOperationNumber">业务宗号</param>
        /// <param name="strPropertyRight">产权人</param>
        /// <param name="strPRNo_Display">产权证号</param>
        /// <param name="strCertificateNo">身份证号</param>
        /// <param name="strReposeName">房屋坐落地址</param>
        /// <returns></returns>
        public static Result GetRelevancyBussiness(string strMappingNo, string strOperationNumber, string strPropertyRight, string strPRNo_Display, string strCertificateNo, string strReposeName)
        {
            string strSql = "select HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo ";

            string strWhere = " where MappingNo like '%" + strMappingNo.Replace("'", "") + "%' and OperationNumber like '%" + strOperationNumber.Replace("'", "") + "%' and PropertyRight like '%" + strPropertyRight.Replace("'", "") + "%'  and PRNo_Display like '%" + strPRNo_Display.Replace("'", "") + "%'  and CertificateNo like '%" + strCertificateNo.Replace("'", "") + "%' and ReposeName like '%" + strReposeName.Replace("'", "") + "%'";

            strSql += " left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id ";

            strSql += strWhere;

            try
            {
                Result rs = SqlClass.GetDataSet(strSql);

                if (rs.DataResult.Tables[0].Rows.Count == 0)
                {
                    return rs;
                }
                string strParentId = rs.DataResult.Tables[0].Rows[0]["ParentId"].ToString();

                string strId = rs.DataResult.Tables[0].Rows[0]["Id"].ToString();

                string strUpSql = "";

                string strDownSql = "";

                while (Convert.ToInt32(strParentId) !=0)
                {
                    strUpSql = "select HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id where  HouseRegInfo.Id=" + strParentId;

                    Result tmpResult = SqlClass.GetDataSet(strUpSql);

                    if (tmpResult.DataResult.Tables[0].Rows.Count < 1)
                    {
                        break;
                    }
                    DataRow newRow = rs.DataResult.Tables[0].NewRow();

                    newRow.ItemArray = tmpResult.DataResult.Tables[0].Rows[0].ItemArray;

                    rs.DataResult.Tables[0].Rows.InsertAt(newRow,0);

                    strParentId = newRow["ParentId"].ToString();
                }
                while (strId !="0")
                {
                    strDownSql = "select HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id where  HouseRegInfo.ParentId=" + strId;

                    Result tmpResult = SqlClass.GetDataSet(strDownSql);

                    if (tmpResult.DataResult.Tables[0].Rows.Count < 1)
                    {
                        break;
                    }
                    DataRow newRow = rs.DataResult.Tables[0].NewRow();

                    newRow.ItemArray = tmpResult.DataResult.Tables[0].Rows[0].ItemArray;

                    rs.DataResult.Tables[0].Rows.Add(newRow);

                    strId = newRow["Id"].ToString();
                }
                
                return rs;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
          /// <summary>
        /// 根据查询条件获取所有相关联业务信息
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="strOperationNumber">业务宗号

        /// <returns></returns>
        public static Result GetRelevancyBussiness(int id)
        {
            string strSql = "select HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo ";

            string strWhere = " where HouseRegInfo.id="+id.ToString() ;

            strSql +="left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id ";

            strSql += strWhere;

            try
            {
                Result rs = SqlClass.GetDataSet(strSql);
                if (rs.DataResult.Tables[0].Rows.Count == 0)
                {
                    return rs;
                }
                string strParentId = rs.DataResult.Tables[0].Rows[0]["ParentId"].ToString();

                string strId = rs.DataResult.Tables[0].Rows[0]["Id"].ToString();

                string strUpSql = "";

                string strDownSql = "";

                while (Convert.ToInt32(strParentId) != 0)
                {
                    strUpSql = "select HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id where  HouseRegInfo.Id=" + strParentId;

                    Result tmpResult = SqlClass.GetDataSet(strUpSql);

                    if (tmpResult.DataResult.Tables[0].Rows.Count < 1)
                    {
                        break;
                    }
                    DataRow newRow = rs.DataResult.Tables[0].NewRow();

                    newRow.ItemArray = tmpResult.DataResult.Tables[0].Rows[0].ItemArray;

                    rs.DataResult.Tables[0].Rows.InsertAt(newRow, 0);

                    strParentId = newRow["ParentId"].ToString();
                }
                while (strId != "0")
                {
                    strDownSql = "select HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo left join RegistrationType on  HouseRegInfo.BussinessTypeId=RegistrationType.Id where HouseRegInfo.ParentId=" + strId;

                    Result tmpResult = SqlClass.GetDataSet(strDownSql);

                    if (tmpResult.DataResult.Tables[0].Rows.Count < 1)
                    {
                        break;
                    }
                    DataRow newRow = rs.DataResult.Tables[0].NewRow();

                    newRow.ItemArray = tmpResult.DataResult.Tables[0].Rows[0].ItemArray;

                    rs.DataResult.Tables[0].Rows.Add(newRow);

                    strId = newRow["Id"].ToString();
                }
                rs.DataResult.Tables[0].Columns.Add("SortNumber", Type.GetType("System.Int32"));

                rs.DataResult.Tables[0].AcceptChanges();

                for (int i = 0; i < rs.DataResult.Tables[0].Rows.Count; i++)
                {
                    rs.DataResult.Tables[0].Rows[i]["SortNumber"] = (i + 1).ToString();
                }
                return rs;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


                /// <summary>
        /// 根据查询条件获取所有相关联业务信息
        /// </summary>
        /// <param name="strMappingNo">测绘编号</param>
        /// <param name="strOperationNumber">业务宗号</param>
        /// <param name="strPropertyRight">产权人</param>
        /// <param name="strPRNo_Display">产权证号</param>
        /// <param name="strCertificateNo">身份证号</param>
        /// <param name="strReposeName">房屋坐落地址</param>
        /// <returns></returns>
        /// ,RegisteDate0,ReadDate0
        public static Result GetRelevancyBussiness(string strMappingNo, string strOperationNumber, string strPropertyRight, string strPRNo_Display, string strCertificateNo, string strReposeName,string RegisterDate,string ReadDate,string RegisteDate0,string ReadDate0,int iCurrentPage, int iPageSize)
        {
            string strSql = "";
            Result rs = new Result();
            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
            try
            {
                int iRecordCount = Convert.ToInt32(SqlHelper.ExecuteScalar(sqlCon, CommandType.Text, "select count(*) from HouseRegInfo left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id"));
                rs.pageInfo= new PageInfo(iCurrentPage, iPageSize, iRecordCount);
                int nextpage = iPageSize * (iCurrentPage - 1);
              
                if (iCurrentPage > 0)
                {
                    string strWhere = " where MappingNo like '%" + strMappingNo.Replace("'", "") + "%' and OperationNumber like '%" + strOperationNumber.Replace("'", "") + "%' and PropertyRight like '%" + strPropertyRight.Replace("'", "") + "%'  and PRNo_Display like '%" + strPRNo_Display.Replace("'", "") + "%'  and CertificateNo like '%" + strCertificateNo.Replace("'", "") + "%' and ReposeName like '%" + strReposeName.Replace("'", "") +"%'";

                    if (RegisterDate != "")
                    {
                        strWhere +=" and HouseRegInfo.CreateTime>Convert(datetime,'" + RegisterDate + "')";
                    }
                    if(ReadDate!="")
                    {
                        strWhere += " and HouseRegInfo.CreateTime<Convert(datetime,'" + ReadDate + "')";
                    }
                    if(RegisteDate0!="")
                    {
                        strWhere+= " and HouseRegInfo.PullDownCertTime>Convert(datetime,'" + RegisteDate0 + "')";
                    }
                    if(ReadDate0!="")
                    {
                        strWhere+=" and  HouseRegInfo.PullDownCertTime<Convert(datetime,'" + RegisteDate0 + "')";
                    }
                    strSql = "select top " + iPageSize + " HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo ";

                    strSql += " left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id ";

                    strSql += strWhere;

                    strSql += " and HouseRegInfo.Id not in";

                    strSql += " (select top " + nextpage + " HouseRegInfo.id from HouseRegInfo ";

                    strSql += strWhere + " order by HouseRegInfo.Id asc)";

                    strSql += " order by HouseRegInfo.Id asc";

                }
                else
                {
                    
                }
                rs.DataResult = SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);

 
                return rs;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
        }

        public static int GetTableCount(string strSql)
        {       
            int iRecordCount=-1;

            SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            sqlCon.Open();

            try
            {                
                iRecordCount = Convert.ToInt32(SqlHelper.ExecuteScalar(sqlCon, CommandType.Text, strSql));               
            }
            catch (Exception ex)
            { 
                iRecordCount=-1;

                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
            return iRecordCount;
        }

        public static DataSet ExecuteDataset(string strSql) 
        {
			SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            DataSet ds = new DataSet();
            try
            {
                sqlCon.Open();
                
                ds=SqlHelper.ExecuteDataset(sqlCon, CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
                sqlCon.Dispose();
            }
            return ds;
        }

		//private static SqlConnection GetSqlCon()
		//{
		//    SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);
		//    return sqlCon;
		//}
        /// <summary>
        /// 更新表中的记录公用方法
        /// </summary>
        /// <param name="SqlTran">数据库事务</param>
        /// <param name="strTableName">表名</param>
        /// <param name="strSetSql">设置的Sql值</param>
        /// <param name="strWhere">Where条件</param>
        /// <returns></returns>
        public static Result UpdateTable(SqlTransaction  SqlTran,string strTableName,string strSetSql,string strWhere)
        {
            Result rs = new Result();

            string strSql = "Update " + strTableName + " " + strSetSql + " " + strWhere;

            try
            {
                int i = SqlHelper.ExecuteNonQuery(SqlTran, CommandType.Text, strSql);

                if (i > 0)
                {
                    rs.IsSuccess = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }
        /// <summary>
        /// 更新表中的记录公用方法
        /// </summary>
        /// <param name="SqlTran">数据库事务</param>
        /// <param name="strTableName">表名</param>
        /// <param name="strWhere">Where条件</param>
        /// <param name="m_SqlParameters">Sql参数和值对象数组</param>
        /// <returns></returns>
        public static Result UpdateTable(SqlTransaction SqlTran, string strTableName, string strWhere, SqlParameter[] m_SqlParameters)
        {
            Result rs = new Result();

            if (m_SqlParameters == null)
            {
                throw new Exception("SqlParameter[]参数不能为空对象！");
            }
            string[] strColumns = new string[m_SqlParameters.GetLength(0)];

            string strSql = "Update " + strTableName + " Set ";

            for (int i = 0; i < m_SqlParameters.GetLength(0); i++)
            {
                strColumns[i] = m_SqlParameters[i].ParameterName.Substring(1);

                strSql += strColumns[i] + "=" + m_SqlParameters[i].ParameterName + ",";

                if (i == m_SqlParameters.GetLength(0) - 1)
                {
                    strSql += strColumns[i] + "=" + m_SqlParameters[i].ParameterName + ",";
                }
                else
                {
                    strSql += strColumns[i] + "=" + m_SqlParameters[i].ParameterName;
                }
            }

             strSql += " " + strWhere;

            try
            {
                int i = SqlHelper.ExecuteNonQuery(SqlTran, CommandType.Text, strSql,m_SqlParameters);

                if (i > 0)
                {
                    rs.IsSuccess = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
		/// <summary>
		/// 执行Sql语句以获取DataTable对象
		/// </summary>
		/// <param name="strSql">Sql语句字符串</param>
		/// <returns></returns>
        public static DataTable ExecuteDataTable(string strSql)
        {
			SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

            DataTable dt = new DataTable();
            try
            {
                sqlCon.Open();

				dt = SqlHelper.ExecuteDataTable(sqlCon, CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlCon.State == System.Data.ConnectionState.Open)
                {
                    sqlCon.Close();
                }
				sqlCon.Dispose();
            }
            return dt;
 
        }
		/// <summary>
		/// 执行分页存储过程方法
		/// </summary>
		/// <param name="strTableName">要查询的表名称和连接的表</param>
		/// <param name="strWhere">Where查询条件</param>
		/// <param name="strSelectColumns">需要选择查询的列名</param>
		/// <param name="CurrentPageIndex">当前页码</param>
		/// <param name="PageSize">每页的记录数大小</param>
		/// <param name="strOrderBy">Order By 排序语句</param>
		/// <param name="PKName">主查询表的主键名称</param>
		/// <param name="TotalCount">记录总数</param>
		/// <returns></returns>
		public static Result ExecPagenationSP(string strTableName,string strWhere,string strSelectColumns,int iCurrentPage,int iPageSize,string strOrderBy,string strPKName)
		{
			Result rs = new Result();

			SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

			sqlCon.Open();

			try
			{
				SqlParameter[] m_SqlParameters = new SqlParameter[8];

				if (!string.IsNullOrEmpty(strTableName))
				{
					m_SqlParameters[0] = new SqlParameter("@strTableName", strTableName);
				}
				else
				{
					m_SqlParameters[0] = new SqlParameter("@strTableName",null);
				}
				if (!string.IsNullOrEmpty(strWhere))
				{
					m_SqlParameters[1] = new SqlParameter("@strWhere", strWhere);
				}
				else
				{
					m_SqlParameters[1] = new SqlParameter("@strWhere",null);
				}
				if (!string.IsNullOrEmpty(strSelectColumns))
				{
					m_SqlParameters[2] = new SqlParameter("@strSelectColumns", strSelectColumns);
				}
				else
				{
					m_SqlParameters[2] = new SqlParameter("@strSelectColumns",null);
				}
				m_SqlParameters[3] = new SqlParameter("@currentPage",iCurrentPage);

				m_SqlParameters[4] = new SqlParameter("@pageSize",iPageSize);

				if (!string.IsNullOrEmpty(strOrderBy))
				{
					m_SqlParameters[5] = new SqlParameter("@orderby", strOrderBy);
				}
				else
				{
					m_SqlParameters[5] = new SqlParameter("@orderby",null);
				}
				if (!string.IsNullOrEmpty(strPKName))
				{
					m_SqlParameters[6] = new SqlParameter("@PKName", strPKName);
				}
				else
				{
					m_SqlParameters[6] = new SqlParameter("@PKName",null);
				}
				m_SqlParameters[7] = new SqlParameter("@total",0);

				m_SqlParameters[7].Direction = ParameterDirection.Output;

				rs.DataResult = SqlHelper.ExecuteDataset(sqlCon,CommandType.StoredProcedure, "sp_Pagination",m_SqlParameters);

				int iTotal = DataConvert.GetInt32Value(m_SqlParameters[7].Value);

				rs.pageInfo = new PageInfo(iCurrentPage, iPageSize, iTotal);

				rs.IsSuccess = true;
			}
			catch (Exception ex)
			{
				rs.IsSuccess = false;
				rs.Msg = ex.Message;
				throw ex;
			}
			finally
			{
				if (sqlCon.State == System.Data.ConnectionState.Open)
				{
					sqlCon.Close();
				}
				sqlCon.Dispose();
			}
			return rs;
		}

        /// <summary>
        /// 执行存储过程，返回DataTable
        /// </summary>
        /// <param name="cmd">存储过程名称</param>
        /// <returns></returns>
        public static DataTable RunProcedureCmd(SqlCommand cmd)
        {
            DataTable result = new DataTable();
            //  Dim conn As New OleDbConnection(Me.connString)
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
            try
            {
                if ((conn.State == ConnectionState.Closed))
                {
                    conn.Open();
                }
                cmd.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(result);
                da.Dispose();
                conn.Close();
                conn.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
                throw ex;
            }
        }


        public static DataTable GetPageList(string TableName,  string ReturnFields, string strwhere, int PageSize, int CurPage,string orderFiledName,int orderType, out int RowCount)
        {


            SqlCommand cmd = new SqlCommand("AspNetPage");//存储过程名称
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tblName", TableName); //表名称
            cmd.Parameters.AddWithValue("@OrderFieldName", orderFiledName);//排序索引字段名
            cmd.Parameters.AddWithValue("@PageIndex", CurPage);//当前第几页，页码
            cmd.Parameters.AddWithValue("@PageSize", PageSize);//每页显示数据条数
            cmd.Parameters.AddWithValue("@SelectFieldName", ReturnFields);//要显示的字段名(不要加select)
            cmd.Parameters.AddWithValue("@OrderType", orderType);//设置排序类型, 非 0 值则降序
            cmd.Parameters.AddWithValue("@strWhere", strwhere);//查询条件(注意: 不要加 where)
            cmd.Parameters.Add(new SqlParameter("@iRowCount", SqlDbType.Int));
            cmd.Parameters["@iRowCount"].Direction = ParameterDirection.Output;
            DataTable dt = RunProcedureCmd(cmd); //请看下面附带的DbHelper方法
            RowCount = Convert.ToInt32(cmd.Parameters["@iRowCount"].Value.ToString()); //返回的总页数
            return dt;
        }
    }
}
